﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class ContactDAO : BaseDAO, IContactDAO
    {
        #region IContactDAO Members

        /// <summary>
        /// Get all contact
        /// </summary>
        /// <param name="includeInactive"></param>
        /// <returns></returns>
        public List<Contact> GetAll(Boolean includeInactive)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "ContactGetAll";

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list contacts
            List<Contact> list = new List<Contact>();

            // For each row in table, add to list contacts
            foreach (DataRow row in dt.Rows)
            {
                Contact c = new Contact();
                c.ContactId = row["ContactID"].ToString();
                c.FirstName = row["FirstName"].ToString();
                c.Surname = row["Surname"].ToString();
                c.KnownAs = row["KnownAs"].ToString();
                c.OfficePhone = row["OfficePhone"].ToString();
                c.MobilePhone = row["MobilePhone"].ToString();
                c.STHomePhone = row["STHomePhone"].ToString();
                c.Email = row["Email"].ToString();
                c.ContactType = new ReferenceDataDAO().GetById(row["ContactType"].ToString());
                c.BestContractMethod = new ReferenceDataDAO().GetById(row["BestContractMethod"].ToString());
                c.JobRole = row["JobRole"].ToString();
                c.Workbase = row["Workbase"].ToString();
                c.JobTitle = row["JobTitle"].ToString();
                c.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(c);
            }

            // Returns list contact
            return list;

        }

        /// <summary>
        /// Get contact by id
        /// </summary>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public Contact GetById(string contactId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "ContactGetById";

            // Add parameters
            comm.Parameters.AddWithValue("@ContactID", contactId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Contact c = null;

            // if there is one row in table, create new contact
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                c = new Contact();
                c.ContactId = row["ContactID"].ToString();
                c.FirstName = row["FirstName"].ToString();
                c.Surname = row["Surname"].ToString();
                c.KnownAs = row["KnownAs"].ToString();
                c.OfficePhone = row["OfficePhone"].ToString();
                c.MobilePhone = row["MobilePhone"].ToString();
                c.STHomePhone = row["STHomePhone"].ToString();
                c.Email = row["Email"].ToString();
                c.ContactType = new ReferenceDataDAO().GetById(row["ContactType"].ToString());
                c.BestContractMethod = new ReferenceDataDAO().GetById(row["BestContractMethod"].ToString());
                c.JobRole = row["JobRole"].ToString();
                c.Workbase = row["Workbase"].ToString();
                c.JobTitle = row["JobTitle"].ToString();
                c.IsActive = bool.Parse(row["IsActive"].ToString());
            }

            // return contact
            return c;
        }

        /// <summary>
        /// Insert new contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public bool Insert(Contact contact)
        {
            return false;
        }

        /// <summary>
        /// Update contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public bool Update(Contact contact)
        {
            return false;
        }

        /// <summary>
        /// Delete contact
        /// </summary>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public bool Delete(string contactId)
        {
            return false;
        }

        /// <summary>
        /// get active contact
        /// </summary>
        /// <returns></returns>
        List<Contact> IContactDAO.GetActiveContact()
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "GetActiveContact";

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list contacts
            List<Contact> list = new List<Contact>();

            // For each row in table, add to list contacts
            foreach (DataRow row in dt.Rows)
            {
                Contact c = new Contact();
                c.ContactId = row["ContactID"].ToString();
                c.FirstName = row["FirstName"].ToString();
                c.Surname = row["Surname"].ToString();
                c.Email = row["Email"].ToString();
                c.MobilePhone = row["MobilePhone"].ToString();
                c.ContactType = (row["ContactType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["ContactType"].ToString()));
                c.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(c);
            }

            // return list contact
            return list;
        }

        List<Contact> IContactDAO.GetAllContact()
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "GetAllContact";

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list contacts
            List<Contact> list = new List<Contact>();

            // For each row in table, add to list contacts
            foreach (DataRow row in dt.Rows)
            {
                Contact c = new Contact();
                c.ContactId = row["ContactID"].ToString();
                c.FirstName = row["FirstName"].ToString();
                c.Surname = row["Surname"].ToString();
                c.Email = row["Email"].ToString();
                c.MobilePhone = row["MobilePhone"].ToString();
                c.ContactType = (row["ContactType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["ContactType"].ToString()));
                c.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(c);
            }

            // return list contact
            return list;
        }

        List<Contact> IContactDAO.GetContactByCodition(string firstname, string surname)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "GetContactByCodition";

            // Add parameters
            comm.Parameters.AddWithValue("@Firstname", firstname);            
            comm.Parameters.AddWithValue("@Surname", surname);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list contacts
            List<Contact> list = new List<Contact>();

            // For each row in table, add to list contacts
            foreach (DataRow row in dt.Rows)
            {
                Contact c = new Contact();
                c.ContactId = row["ContactID"].ToString();
                c.FirstName = row["FirstName"].ToString();
                c.Surname = row["Surname"].ToString();
                c.Email = row["Email"].ToString();
                c.MobilePhone = row["MobilePhone"].ToString();
                c.ContactType = (row["ContactType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["ContactType"].ToString()));
                c.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(c);
            }

            // return list contact
            return list;
        }

        #endregion
    }
}
